﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
using VentaElectrodomesticos.Utilities;

namespace VentaElectrodomesticos.Sql
{
    public class ClaseSQL
    {
        private static string connectString = "Data Source=localhost\\SQLSERVER2005;Initial Catalog=GD1C2011; User ID=gd;Password=gd2011";
        private SqlConnection cnn;

        public ClaseSQL()
        {
            cnn = new SqlConnection(connectString);
        }

        public void Open()
        {
            cnn.Open();
        }

        public void Close()
        {
            if (cnn.State != ConnectionState.Closed)
            {
                cnn.Close();
            }
        }




        /// <summary>
        /// Devuelve un DataTable con los datos resultantes de la query.
        /// </summary>
        /// <param name="query">Query SQL</param>
        /// <returns>DataTable con los datos resultantes de la query.</returns>
        public DataTable getDataTable(String query)
        {
            try
            {
                SqlCommand command = new SqlCommand();
                command.Connection = cnn;
                command.CommandType = System.Data.CommandType.Text;
                command.CommandText = query;
                SqlDataReader reader = command.ExecuteReader();
                DataTable dataTable = new DataTable();
                dataTable.Load(reader);
                reader.Close();

                return dataTable;
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }



        /// <summary>
        /// Usar este metodo para ejecutar inserts, updates y deletes.
        /// </summary>
        /// <param name="query">Query SQL (no reader): insert, update, delete.</param>
        /// <returns>Cantidad de filas afectadas.</returns>
        public int ejecutarNoReader(String query)
        {
            try
            {
                SqlCommand command = new SqlCommand();
                command.Connection = cnn;
                command.CommandType = System.Data.CommandType.Text;
                command.CommandText = query;
                int filasAfectadas = command.ExecuteNonQuery();

                return filasAfectadas;
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }

        public SqlDataReader busquedaSQLDataReader(String query)
        {
            try
            {
                SqlCommand command = new SqlCommand();
                command.Connection = cnn;
                command.CommandType = System.Data.CommandType.Text;
                command.CommandText = query;
                SqlDataReader reader = command.ExecuteReader();

                return reader;
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }

        public SqlDataReader ejecutarStoredProcedure(string sp, String[,] parametros)
        {
            try
            {
                SqlCommand command = new SqlCommand();
                command.Connection = cnn;
                command.CommandType = System.Data.CommandType.StoredProcedure;
                command.CommandText = sp;
                command.CommandTimeout = 0;

                for (int i = 0; i < parametros.GetLength(1); i++)
                {
                    command.Parameters.AddWithValue(parametros[0, i], parametros[1, i]);
                }
                return command.ExecuteReader();
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message + "\n" + ex.StackTrace, "Error");
                throw ex;
            }
        }

        public String ejecutarStoredProcedureConRetorno(string sp, String[,] parametros, String retorno)
        {
            try
            {
                SqlCommand command = new SqlCommand();
                command.Connection = cnn;
                command.CommandType = System.Data.CommandType.StoredProcedure;
                command.CommandText = sp;
                command.CommandTimeout = 10;

                for (int i = 0; i < parametros.GetLength(1); i++)
                {
                    command.Parameters.AddWithValue(parametros[0, i], parametros[1, i]);
                }
                command.Parameters.Add(retorno, SqlDbType.VarChar);
                command.Parameters[retorno].Direction = ParameterDirection.ReturnValue;
                command.ExecuteNonQuery();

                return command.Parameters[retorno].Value.ToString();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }

        public SqlDataReader ejecutarFuncion(string func, String[,] parametros)
        {
            try
            {
                SqlCommand command = new SqlCommand();
                command.Connection = cnn;
                command.CommandType = System.Data.CommandType.Text;
                command.CommandText = "SELECT " + func + "as functionresult";
                command.CommandTimeout = 10;

                for (int i = 0; i < parametros.GetLength(1); i++)
                {
                    command.Parameters.AddWithValue(parametros[0, i], parametros[1, i]);
                }
                return command.ExecuteReader();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }

        public void llenarCombo(ComboBox cb, String query)
        {
            try
            {
                this.Open();

                SqlDataReader reader = this.busquedaSQLDataReader(query);
                while (reader.Read())
                {
                    cb.Items.Add(new KeyValuePair(reader[0].ToString().Trim(), reader[1].ToString().Trim()));
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error al llenar combobox");
            }
            finally
            {
                this.Close();
            }
        }

        public void llenarComboSimple(ComboBox cb, String query)
        {
            try
            {
                this.Open();

                SqlDataReader reader = this.busquedaSQLDataReader(query);
                while (reader.Read())
                {
                    cb.Items.Add( reader[0].ToString().Trim() );
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error al llenar combobox");
            }
            finally
            {
                this.Close();
            }
        }

        public bool bajaRegistro(String tabla, String campoClave, String valorCampoClave)
        {
            bool exito = false; 

            if (MessageBox.Show("Esta seguro que desea borrar este registro?", "Confirmar eliminacion", MessageBoxButtons.YesNo) == DialogResult.Yes)
            {
                // Baja logica del registro           
                try
                {
                    this.Open();

                    String query = "UPDATE " + tabla +
                        " SET status = '0'" +
                        " WHERE " + campoClave + " = " + valorCampoClave;

                    int filasAfectadas = this.ejecutarNoReader(query);
                    if (filasAfectadas == 1)
                    {
                        MessageBox.Show("Se ha dado de baja correctamente");
                        exito = true;
                    }
                    
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "Error al intentar eliminar");
                }
                finally
                {
                    this.Close();
                }                
            }
            return exito;
        }

        public bool habilitarRegistro(String tabla, String campoClave, String valorCampoClave)
        {
            bool exito = false;

            if (MessageBox.Show("Esta seguro que desea habilitar este registro?", "Confirmar habilitacion", MessageBoxButtons.YesNo) == DialogResult.Yes)
            {    
                try
                {
                    this.Open();

                    String query = "UPDATE " + tabla +
                        " SET status = '1'" +
                        " WHERE " + campoClave + " = " + valorCampoClave;

                    int filasAfectadas = this.ejecutarNoReader(query);
                    if (filasAfectadas == 1)
                    {
                        MessageBox.Show("Se ha habilitado correctamente");
                        exito = true;
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "Error al intentar habilitar");
                }
                finally
                {
                    this.Close();
                }
            }
            return exito;
        }

    }


    

}
